package com.scalar.chiptrack.adaptor.spil;

import com.scalar.ScalarException;
import com.scalar.chiptrack.adaptor.AdaptorConstants;
import com.scalar.chiptrack.adaptor.AdaptorUtils;
import com.scalar.chiptrack.adaptor.AssemblyLot;
import com.scalar.chiptrack.adaptor.InventoryLot;
import com.scalar.chiptrack.adaptor.TestLot;
import com.scalar.chiptrack.adaptor.FabSortLot;

import com.scalar.chiptrack.adaptor.dao.FabSortAdaptorDAO;
import com.scalar.chiptrack.adaptor.dao.TestAdaptorDAO;
import com.scalar.chiptrack.adaptor.dao.AssemblyAdaptorDAO;
import com.scalar.chiptrack.adaptor.dao.InventoryLotDAO;
import com.scalar.chiptrack.adaptor.dao.AdaptorDAO;
import com.scalar.chiptrack.adaptor.dao.AdaptorRunInfo;

import com.scalar.chiptrack.utils.Logger;
import com.scalar.chiptrack.utils.StringUtils;
import com.scalar.chiptrack.jobs.JobHandler;

import java.io.File;

import java.util.HashMap;
import java.util.ArrayList;
import java.util.Iterator;
import java.util.Calendar;
import java.util.GregorianCalendar;
import java.util.TimeZone;
import java.util.Locale;

import jxl.Workbook;
import jxl.Sheet;
import jxl.Cell;
import jxl.CellType;
import jxl.DateCell;
import jxl.read.biff.BiffException;


/**
 * User: ChandraSekhar
 * Date: Feb 7, 2008
 * Time: 3:12:50 PM
 */
public class SPILDataParser extends JobHandler implements AdaptorConstants
{
    private HashMap m_sortDataMap           = new HashMap();
    private HashMap m_dieBankDataMap        = new HashMap();
    private HashMap m_assemblyDataMap       = new HashMap();
    private HashMap m_ftDataMap           = new HashMap();
    private HashMap m_fgDataMap             = new HashMap();
    private HashMap m_waferBankDataMap     = new HashMap();
    private HashMap m_markPackDataMap       = new HashMap();
    
    private Logger m_parserLogger   = null;
    private String m_dataOutputDir  = null;

    public SPILDataParser() throws ScalarException
    {
        try
        {
            m_dataOutputDir = AdaptorUtils.getDataOutputDir(VENDOR_NAME_SPIL_CHINA, WIP_TYPE_ASSEMBLY);
            AdaptorUtils.makeAllAdaptorDirs( m_dataOutputDir );
            m_parserLogger = AdaptorUtils.getDataParserLogger( m_dataOutputDir,VENDOR_NAME_SPIL_CHINA );
            AssemblyAdaptorDAO.setLogger( m_parserLogger );
            TestAdaptorDAO.setLogger( m_parserLogger );
            InventoryLotDAO.setLogger(m_parserLogger);
            FabSortAdaptorDAO.setLogger(m_parserLogger);
            AdaptorUtils.setLogger( m_parserLogger );
        }
        catch (ScalarException se)
        {
            log("ERROR!! ERROR!! ERROR!! Exception while loading SPIL WIP output directory details from database, Exception: " + se);
            throw se;
        }
    }
    /**
     *
     * @param args
     * @throws Exception
     */
    public static void main( String args[] ) throws Exception
    {
        try
        {
            new SPILDataParser().execute();
        }
        catch (Exception e)
        {
            throw e;
        }
    }
    /**
     *
     * @throws Exception
     */
    public void execute() throws Exception
    {
        AdaptorUtils.printHeader(VENDOR_NAME_SPIL_CHINA, CONSTANT_PARSER );

        File doneDestDir = new File(  m_dataOutputDir + "/done" );

        File sourceDataoutDir = new File( m_dataOutputDir );
        
        File[] fileList = sourceDataoutDir.listFiles();
        if ( ( fileList == null ) || ( fileList.length <= 3 ) )
        {
            log( "No file found in directory: " + sourceDataoutDir );
            throw new ScalarException( "\n+-----------No file found in directory " + sourceDataoutDir );
        }


        ArrayList successFileList = new ArrayList();

        boolean success = false;

        for ( int i = 0; i < fileList.length; i++ )
        {
            if ( fileList[i].isDirectory() == true )
            {
                continue;
            }

            try
            {
                // Step 1
                // Parse Files to get Object List
                if ( fileList[i].getName() != null && fileList[i].getName().trim().startsWith( SPILChinaPOP3Adaptor.LS_WIP_FILE_NAME ) )
                {
                    try
                    {
                        // To parse the the SPIL_WIP_STATUS sheet in to table
                        ArrayList spilGSTLWipList = parseSpilWipReport( fileList[i].getAbsolutePath() );

                        if ( spilGSTLWipList != null && spilGSTLWipList.size() > 0 )
                        {
                            //to get the Spil Wip Details details
                            getSheetDetails( spilGSTLWipList );
                        }
                    }
                    catch( BiffException e)
                    {

                    }
                    successFileList.add( fileList[i] );
                }

                 // Step 1
                // Parse Files to get Object List
                if ( fileList[i].getName() != null && fileList[i].getName().trim().startsWith( SPILChinaPOP3Adaptor.GSTL_WIP_FILE_NAME ) )
                {
                     // To parse the the SPIL_LS_STATUS
                    ArrayList spilLSWipList = parseSpilWipReport( fileList[i].getAbsolutePath() );

                    if ( spilLSWipList != null && spilLSWipList.size() > 0 )
                    {
                        //to get the Spil Wip Details details
                        getSheetDetails( spilLSWipList );
                    }

                    successFileList.add( fileList[i] );
                }
                success = true;
            }
            catch ( Exception e )
            {
                success = false;
                log( "Exception while parsing and storing file: " + fileList[i].getAbsolutePath() + "\nException: " + e );
                e.printStackTrace();
                throw e;
            }
        }
        /*Updating the Wafer Store Lot Information*/
        try
        {
            // Save Wafer Store Information
            if (m_waferBankDataMap.isEmpty() == false)
            {
                log(" Updating Wafer Store Lot Information");
                InventoryLotDAO.saveInventoryLot(null, m_waferBankDataMap.values().iterator(), LOCATION_TYPE_WAFER_STORE, VENDOR_NAME_SPIL_CHINA);
            }
            else
            {
                InventoryLotDAO.closeInventoryLot(null, LOCATION_TYPE_WAFER_STORE, VENDOR_NAME_SPIL_CHINA);
            }
            success = true;
            m_waferBankDataMap.clear();
            m_waferBankDataMap = null;
        }
        catch (ScalarException se)
        {
            success = false;
            se.printStackTrace();
            log("ERROR!! ERROR!! ERROR!! Could not update Wafer Store Lot information into database: " + se);
            throw se;
        }

        //to saving sort Lot Info
        try
        {
            // Save Sort Information
            if ( m_sortDataMap.isEmpty() == false )
            {
                log( " Updating Sort Lot Information" );
                FabSortAdaptorDAO.saveSortLot( null, m_sortDataMap.values().iterator(), WIP_TYPE_SORT, VENDOR_NAME_SPIL_CHINA );
            }
            else
            {
                FabSortAdaptorDAO.closeFabSortLot( null, WIP_TYPE_SORT, VENDOR_NAME_SPIL_CHINA );
            }
            success = true;
            m_sortDataMap.clear();
            m_sortDataMap = null;
        }
        catch (ScalarException se)
        {
            success = false;
            se.printStackTrace();
            log( "ERROR!! ERROR!! ERROR!! Could not update Sort Lot information into database: " + se );
            throw se;
        }

        /*Updating the Die Bank Lot Information*/
        try
        {
            // Save Die Bank Information
            if (m_dieBankDataMap.isEmpty() == false)
            {
                log(" Updating Die Bank Lot Information");
                InventoryLotDAO.saveInventoryLot(null, m_dieBankDataMap.values().iterator(), LOCATION_TYPE_DIE_BANK, VENDOR_NAME_SPIL_CHINA);
            }
            else
            {
                InventoryLotDAO.closeInventoryLot(null, LOCATION_TYPE_DIE_BANK, VENDOR_NAME_SPIL_CHINA);
            }
            success = true;
            m_dieBankDataMap.clear();
            m_dieBankDataMap = null;
        }
        catch (ScalarException se)
        {
            success = false;
            se.printStackTrace();
            log("ERROR!! ERROR!! ERROR!! Could not update Die Bank Lot information into database: " + se);
            throw se;
        }
        /*Assembly Lot Saving*/
        try
        {
            // Save Assembly Information
            if ( m_assemblyDataMap.isEmpty() == false )
            {
                log( " Updating Assembly Lot Information" );
                AssemblyAdaptorDAO.saveAssemblyLot( null, m_assemblyDataMap.values().iterator(),WIP_TYPE_ASSEMBLY, VENDOR_NAME_SPIL_CHINA );
            }
            else
            {
                AssemblyAdaptorDAO.closeAssemblyLot( null, WIP_TYPE_ASSEMBLY, VENDOR_NAME_SPIL_CHINA);
            }
            success = true;
            m_assemblyDataMap.clear();
            m_assemblyDataMap = null;
        }
        catch (ScalarException se)
        {
            success = false;
            se.printStackTrace();
            log( "ERROR!! ERROR!! ERROR!! Could not update Assembly Lot information into database: " + se );
            throw se;
        }
        // Saving the Test Lot Information
        try
        {
            // Save Final Test Information
            if ( m_ftDataMap.isEmpty() == false )
            {
                log( " Updating Test Lot Information" );
                TestAdaptorDAO.saveTestLot( null, m_ftDataMap.values().iterator(), LOCATION_TYPE_FT, VENDOR_NAME_SPIL_CHINA );
            }
            else
            {
                TestAdaptorDAO.closeTestLot( null, LOCATION_TYPE_FT, VENDOR_NAME_SPIL_CHINA );
            }

            success = true;
            m_ftDataMap.clear();
            m_ftDataMap = null;
        }
        catch (ScalarException se)
        {
            success = false;
            se.printStackTrace();
            log( "ERROR!! ERROR!! ERROR!! Could not update Test Lot information into database: " + se );
            throw se;
        }

        // to save the Mark & Pack lot tables
       try
       {
           if ( m_markPackDataMap.isEmpty() == false )
           {
               log(" Updating Mark & Pack Lot Information");
               InventoryLotDAO.saveInventoryLot( null, m_markPackDataMap.values().iterator(), LOCATION_TYPE_BAKE_DRY, VENDOR_NAME_SPIL_CHINA );
               success = true;
           }
           else
           {
               InventoryLotDAO.closeInventoryLot(null, LOCATION_TYPE_BAKE_DRY, VENDOR_NAME_SPIL_CHINA  );
           }
      }
      catch (ScalarException se)
      {
          success = false;
          se.printStackTrace();
          log("ERROR!! ERROR!! ERROR!! Could not update Mark & Pack Lot information into database: " + se);
          throw se;
      }

        /*Updating the Finished Goods Lot Information*/
        try
        {
            // Save Finished Goods Information
            if (m_fgDataMap.isEmpty() == false)
            {
                log(" Updating Finished Goods Lot Information");
                InventoryLotDAO.saveInventoryLot(null, m_fgDataMap.values().iterator(), LOCATION_TYPE_FG, VENDOR_NAME_SPIL_CHINA);
            }
            else
            {
                InventoryLotDAO.closeInventoryLot(null, LOCATION_TYPE_FG, VENDOR_NAME_SPIL_CHINA);
            }
            success = true;
            m_fgDataMap.clear();
            m_fgDataMap = null;
        }
        catch (ScalarException se)
        {
            success = false;
            se.printStackTrace();
            log("ERROR!! ERROR!! ERROR!! Could not update Fg Lot information into database: " + se);
            throw se;
        }

         // TO SEND THE NEW LOTS, NEW TRANSIT LOTS, NO PO LOTS, DISCREPANCY LOTS NOTIFICATIONS TO THE USER
        AdaptorDAO.sendAllNotifications(VENDOR_NAME_SPIL_CHINA, true, true, true);

        // To Close the Drop Out Lots
        AdaptorDAO.closeDropOutLots(VENDOR_NAME_SPIL_CHINA);

        int adaptor_id = AdaptorRunInfo.getLastRunAdaptorId( SPILChinaPOP3Adaptor.ADAPTOR_NAME, CONSTANT_ADAPTOR);

        // Update Adaptor_Run_Info table
        try
        {
            if ( success == true )
            {
                AdaptorRunInfo.saveAdaptorLastUpdatedStatus(CONSTANT_PARSER, SPILChinaPOP3Adaptor.ADAPTOR_NAME, CONSTANT_SUCCESS, adaptor_id);
                System.out.println( "SUCCESSFULLY UPDATED IN THE DATABASE" );
            }
        }
        catch ( ScalarException se )
        {
            success = false; //As filES DATA SAVED , UPDATEING AS SUCCESS
            AdaptorRunInfo.saveAdaptorLastUpdatedStatus(CONSTANT_PARSER, SPILChinaPOP3Adaptor.ADAPTOR_NAME, CONSTANT_SUCCESS, adaptor_id);
            log( "WARNING!! WARNING!! WARNING!! Could not update Adaptor run information, Exception: " + se );
            throw se;
        }
        // Step 7
        // Move the successful files to 'done' directory
       if (success)
       {
            AdaptorUtils.moveFilesToDone(successFileList, doneDestDir);
       }
    }
    /**
     *
     * @param fileName
     * @return
     * @throws Exception
     */
    private ArrayList parseSpilWipReport( String fileName ) throws Exception
    {
        if ( fileName == null ) throw new ScalarException( "Filename argument is null" );

        SPILWipStatus spilWipStatus = null;
        ArrayList spilWipStatusList = new ArrayList();
         String checkValue;
        try
        {
            File lotstatusFile = new File( fileName );
            if ( lotstatusFile.exists() == false )
            {
                throw new ScalarException( fileName + " doesnt exist" );
            }

            log( "\nFound " + fileName );
            log( "Parsing the file for Legend Spil WIP Status Report..." );
            System.out.println("\nFound " + fileName);
            System.out.println("Parsing the file for Legend Spil WIP Status Report...");

            Workbook workbook = Workbook.getWorkbook(lotstatusFile);
            Sheet sheet = workbook.getSheet(0);

            Cell[] row = null;

            for ( int rowCount = 4; rowCount < sheet.getRows(); rowCount++ )
            {
                row = sheet.getRow( rowCount );

                spilWipStatus = new SPILWipStatus();

                checkValue = row[1].getContents().trim();

                if( checkValue != null && checkValue.trim().length() > 0 )
                {
                    if (row.length > 0)
                    {
                        if (row[0].getType().equals(CellType.EMPTY))
                        {
                            spilWipStatus.setCustomerLot(null);
                        }
                        else
                        {
                            spilWipStatus.setCustomerLot(row[0].getContents().trim());
                        }
                    }
                    if (row.length > 1)
                    {
                        if (row[1].getType().equals(CellType.EMPTY))
                        {
                            spilWipStatus.setProdLot(null);
                        }
                        else
                        {
                            spilWipStatus.setProdLot(row[1].getContents().trim());
                        }
                    }
                    if (row.length > 2)
                    {
                        if (row[2].getType().equals(CellType.EMPTY))
                        {
                            spilWipStatus.setDevice(null);
                        }
                        else
                        {
                            spilWipStatus.setDevice(row[2].getContents().trim());
                        }
                    }
                    if (row.length > 3)
                    {
                        if (row[3].getType().equals(CellType.EMPTY))
                        {
                            spilWipStatus.setPackageType(null);
                        }
                        else
                        {
                            spilWipStatus.setPackageType(row[3].getContents().trim());
                        }
                    }
                    if (row.length > 4)
                    {
                        if (row[4].getType().equals(CellType.EMPTY))
                        {
                            spilWipStatus.setLeadCount(null);
                        }
                        else
                        {
                            spilWipStatus.setLeadCount(row[4].getContents().trim());
                        }
                    }
                    if (row.length > 5)
                    {
                        if (row[5].getType().equals(CellType.EMPTY))
                        {
                            spilWipStatus.setWsrcv(null);
                        }
                        else if( row[5].getType().equals( CellType.DATE ))
                        {
                            DateCell dc = (DateCell) row[5];
                            java.util.Date wsRcv = null;
                            wsRcv = dc.getDate();

                            if ("America/Los_Angeles".equals(System.getProperty("user.timezone")))
                            {
                                Calendar cal = Calendar.getInstance(TimeZone.getTimeZone("GMT"), Locale.US);
                                cal.setTime(wsRcv);
                                wsRcv = new GregorianCalendar(cal.get(Calendar.YEAR), cal.get(Calendar.MONTH), cal.get(Calendar.DATE)).getTime();
                                spilWipStatus.setWsrcv(StringUtils.formatDateToString(wsRcv));
                            }
                            else
                            {
                                Calendar cal = new GregorianCalendar();
                                cal.setTime(wsRcv);
                                wsRcv = new GregorianCalendar(cal.get(Calendar.YEAR), cal.get(Calendar.MONTH), cal.get(Calendar.DATE)).getTime();
                                spilWipStatus.setWsrcv(StringUtils.formatDateToString(wsRcv));
                            }
                        }
                    }
                    if (row.length > 6)
                    {
                        if (row[6].getType().equals(CellType.EMPTY))
                        {
                            spilWipStatus.setWsInQty(null);
                        }
                        else
                        {
                            spilWipStatus.setWsInQty(row[6].getContents().trim());
                        }
                    }
                    if (row.length > 7)
                    {
                        if (row[7].getType().equals(CellType.EMPTY))
                        {
                            spilWipStatus.setWsbk(null);
                        }
                        else
                        {
                            spilWipStatus.setWsbk(row[7].getContents().trim());
                        }
                    }
                    if (row.length > 8)
                    {
                        if (row[8].getType().equals(CellType.EMPTY))
                        {
                            spilWipStatus.setProbe(null);
                        }
                        else
                        {
                            spilWipStatus.setProbe(row[8].getContents().trim());
                        }
                    }
                    if (row.length > 9)
                    {
                        if (row[9].getType().equals(CellType.EMPTY))
                        {
                            spilWipStatus.setWsfgWafer(null);
                        }
                        else
                        {
                            spilWipStatus.setWsfgWafer(row[9].getContents().trim());
                        }
                    }
                    if (row.length > 10)
                    {
                        if (row[10].getType().equals(CellType.EMPTY))
                        {
                            spilWipStatus.setWsfgDie(null);
                        }
                        else
                        {
                            spilWipStatus.setWsfgDie(row[10].getContents().trim());
                        }
                    }
                    if (row.length > 11)
                    {
                        if (row[11].getType().equals(CellType.EMPTY))
                        {
                            spilWipStatus.setWsSOD(null);
                        }
                        else if( row[11].getType().equals( CellType.DATE ))
                        {
                            DateCell dc = (DateCell) row[11];
                            java.util.Date wsSod = null;
                            wsSod = dc.getDate();

                            if ("America/Los_Angeles".equals(System.getProperty("user.timezone")))
                            {
                                Calendar cal = Calendar.getInstance(TimeZone.getTimeZone("GMT"), Locale.US);
                                cal.setTime(wsSod);
                                wsSod = new GregorianCalendar(cal.get(Calendar.YEAR), cal.get(Calendar.MONTH), cal.get(Calendar.DATE)).getTime();
                                spilWipStatus.setWsSOD(StringUtils.formatDateToString(wsSod));
                            }
                            else
                            {
                                Calendar cal = new GregorianCalendar();
                                cal.setTime(wsSod);
                                wsSod = new GregorianCalendar(cal.get(Calendar.YEAR), cal.get(Calendar.MONTH), cal.get(Calendar.DATE)).getTime();
                                spilWipStatus.setWsSOD(StringUtils.formatDateToString(wsSod));
                            }
                        }
                        else
                        {
                            spilWipStatus.setWsSOD(row[11].getContents().trim());
                        }
                    }
                    if (row.length > 12)
                    {
                        if (row[12].getType().equals(CellType.EMPTY))
                        {
                            spilWipStatus.setWsTran(null);
                        }
                        else
                        {
                            spilWipStatus.setWsTran(row[12].getContents().trim());
                        }
                    }
                    if (row.length > 13)
                    {
                        if (row[13].getType().equals(CellType.EMPTY))
                        {
                            spilWipStatus.setAsRcvDate(null);
                        }
                         else if( row[13].getType().equals( CellType.DATE ))
                        {
                            DateCell dc = (DateCell) row[13];
                            java.util.Date asRcvDate = null;
                            asRcvDate = dc.getDate();

                            if ("America/Los_Angeles".equals(System.getProperty("user.timezone")))
                            {
                                Calendar cal = Calendar.getInstance(TimeZone.getTimeZone("GMT"), Locale.US);
                                cal.setTime(asRcvDate);
                                asRcvDate = new GregorianCalendar(cal.get(Calendar.YEAR), cal.get(Calendar.MONTH), cal.get(Calendar.DATE)).getTime();
                                spilWipStatus.setAsRcvDate(StringUtils.formatDateToString(asRcvDate));
                            }
                            else
                            {
                                Calendar cal = new GregorianCalendar();
                                cal.setTime(asRcvDate);
                                asRcvDate = new GregorianCalendar(cal.get(Calendar.YEAR), cal.get(Calendar.MONTH), cal.get(Calendar.DATE)).getTime();
                                spilWipStatus.setAsRcvDate(StringUtils.formatDateToString(asRcvDate));
                            }
                        }
                    }
                    if (row.length > 14)
                    {
                        if (row[14].getType().equals(CellType.EMPTY))
                        {
                            spilWipStatus.setAsRcvDie(null);
                        }
                        else
                        {
                            spilWipStatus.setAsRcvDie(row[14].getContents().trim());
                        }
                    }
                    if (row.length > 15)
                    {
                        if (row[15].getType().equals(CellType.EMPTY))
                        {
                            spilWipStatus.setAsBkWafer(null);
                        }
                        else
                        {
                            spilWipStatus.setAsBkWafer(row[15].getContents().trim());
                        }
                    }
                    if (row.length > 16)
                    {
                        if (row[16].getType().equals(CellType.EMPTY))
                        {
                            spilWipStatus.setAsBkDie(null);
                        }
                        else
                        {
                            spilWipStatus.setAsBkDie(row[16].getContents().trim());
                        }
                    }
                    if (row.length > 17)
                    {
                        if (row[17].getType().equals(CellType.EMPTY))
                        {
                            spilWipStatus.setLoadDate(null);
                        }
                        else if( row[17].getType().equals( CellType.DATE ))
                        {
                            DateCell dc = (DateCell) row[17];
                            java.util.Date loadDate = null;
                            loadDate = dc.getDate();

                            if ("America/Los_Angeles".equals(System.getProperty("user.timezone")))
                            {
                                Calendar cal = Calendar.getInstance(TimeZone.getTimeZone("GMT"), Locale.US);
                                cal.setTime(loadDate);
                                loadDate = new GregorianCalendar(cal.get(Calendar.YEAR), cal.get(Calendar.MONTH), cal.get(Calendar.DATE)).getTime();
                                spilWipStatus.setLoadDate(StringUtils.formatDateToString(loadDate));
                            }
                            else
                            {
                                Calendar cal = new GregorianCalendar();
                                cal.setTime(loadDate);
                                loadDate = new GregorianCalendar(cal.get(Calendar.YEAR), cal.get(Calendar.MONTH), cal.get(Calendar.DATE)).getTime();
                                spilWipStatus.setLoadDate(StringUtils.formatDateToString(loadDate));
                            }
                        }
                    }
                    if (row.length > 18)
                    {
                        if (row[18].getType().equals(CellType.EMPTY))
                        {
                            spilWipStatus.setAsActualQty(null);
                        }
                        else
                        {
                            spilWipStatus.setAsActualQty(row[18].getContents().trim());
                        }
                    }
                    if (row.length > 19)
                    {
                        if (row[19].getType().equals(CellType.EMPTY))
                        {
                            spilWipStatus.setOpticalInspect(null);
                        }
                        else
                        {
                            spilWipStatus.setOpticalInspect(row[19].getContents().trim());
                        }
                    }
                    if (row.length > 20)
                    {
                        if (row[20].getType().equals(CellType.EMPTY))
                        {
                            spilWipStatus.setDieAttach(null);
                        }
                        else
                        {
                            spilWipStatus.setDieAttach(row[20].getContents().trim());
                        }
                    }
                    if (row.length > 21)
                    {
                        if (row[21].getType().equals(CellType.EMPTY))
                        {
                            spilWipStatus.setWireBond(null);
                        }
                        else
                        {
                            spilWipStatus.setWireBond(row[21].getContents().trim());
                        }
                    }
                    if (row.length > 22)
                    {
                        if (row[22].getType().equals(CellType.EMPTY))
                        {
                            spilWipStatus.setMolding(null);
                        }
                        else
                        {
                            spilWipStatus.setMolding(row[22].getContents().trim());
                        }
                    }
                    if (row.length > 23)
                    {
                        if (row[23].getType().equals(CellType.EMPTY))
                        {
                            spilWipStatus.setPlating(null);
                        }
                        else
                        {
                            spilWipStatus.setPlating(row[23].getContents().trim());
                        }
                    }
                    if (row.length > 24)
                    {
                        if (row[24].getType().equals(CellType.EMPTY))
                        {
                            spilWipStatus.setForming(null);
                        }
                        else
                        {
                            spilWipStatus.setForming(row[24].getContents().trim());
                        }
                    }
                    if (row.length > 25)
                    {
                        if (row[25].getType().equals(CellType.EMPTY))
                        {
                            spilWipStatus.setPacking(null);
                        }
                        else
                        {
                            spilWipStatus.setPacking(row[25].getContents().trim());
                        }
                    }
                    if (row.length > 26)
                    {
                        if (row[26].getType().equals(CellType.EMPTY))
                        {
                            spilWipStatus.setAsSod(null);
                        }
                        else if( row[26].getType().equals( CellType.DATE ))
                        {
                            DateCell dc = (DateCell) row[26];
                            java.util.Date asSod = null;
                            asSod = dc.getDate();

                            if ("America/Los_Angeles".equals(System.getProperty("user.timezone")))
                            {
                                Calendar cal = Calendar.getInstance(TimeZone.getTimeZone("GMT"), Locale.US);
                                cal.setTime(asSod);
                                asSod = new GregorianCalendar(cal.get(Calendar.YEAR), cal.get(Calendar.MONTH), cal.get(Calendar.DATE)).getTime();
                                spilWipStatus.setAsSod(StringUtils.formatDateToString(asSod));
                            }
                            else
                            {
                                Calendar cal = new GregorianCalendar();
                                cal.setTime(asSod);
                                asSod = new GregorianCalendar(cal.get(Calendar.YEAR), cal.get(Calendar.MONTH), cal.get(Calendar.DATE)).getTime();
                                spilWipStatus.setAsSod(StringUtils.formatDateToString(asSod));
                            }
                        }
                    }
                    if (row.length > 27)
                    {
                        if (row[27].getType().equals(CellType.EMPTY))
                        {
                            spilWipStatus.setAsrSod(null);
                        }
                        else if( row[27].getType().equals( CellType.DATE ))
                        {
                            DateCell dc = (DateCell) row[27];
                            java.util.Date asRsod = null;
                            asRsod = dc.getDate();

                            if ("America/Los_Angeles".equals(System.getProperty("user.timezone")))
                            {
                                Calendar cal = Calendar.getInstance(TimeZone.getTimeZone("GMT"), Locale.US);
                                cal.setTime(asRsod);
                                asRsod = new GregorianCalendar(cal.get(Calendar.YEAR), cal.get(Calendar.MONTH), cal.get(Calendar.DATE)).getTime();
                                spilWipStatus.setAsrSod(StringUtils.formatDateToString(asRsod));
                            }
                            else
                            {
                                Calendar cal = new GregorianCalendar();
                                cal.setTime(asRsod);
                                asRsod = new GregorianCalendar(cal.get(Calendar.YEAR), cal.get(Calendar.MONTH), cal.get(Calendar.DATE)).getTime();
                                spilWipStatus.setAsrSod(StringUtils.formatDateToString(asRsod));
                            }
                        }
                    }
                    if (row.length > 28)
                    {
                        if (row[28].getType().equals(CellType.EMPTY))
                        {
                            spilWipStatus.setAsTran(null);
                        }
                        else
                        {
                            spilWipStatus.setAsTran(row[28].getContents().trim());
                        }
                    }
                    if (row.length > 29)
                    {
                        if (row[29].getType().equals(CellType.EMPTY))
                        {
                            spilWipStatus.setFtRcv(null);
                        }
                         else if( row[29].getType().equals( CellType.DATE ))
                        {
                            DateCell dc = (DateCell) row[29];
                            java.util.Date ftRcv = null;
                            ftRcv = dc.getDate();

                            if ("America/Los_Angeles".equals(System.getProperty("user.timezone")))
                            {
                                Calendar cal = Calendar.getInstance(TimeZone.getTimeZone("GMT"), Locale.US);
                                cal.setTime(ftRcv);
                                ftRcv = new GregorianCalendar(cal.get(Calendar.YEAR), cal.get(Calendar.MONTH), cal.get(Calendar.DATE)).getTime();
                                spilWipStatus.setFtRcv(StringUtils.formatDateToString(ftRcv));
                            }
                            else
                            {
                                Calendar cal = new GregorianCalendar();
                                cal.setTime(ftRcv);
                                ftRcv = new GregorianCalendar(cal.get(Calendar.YEAR), cal.get(Calendar.MONTH), cal.get(Calendar.DATE)).getTime();
                                spilWipStatus.setFtRcv(StringUtils.formatDateToString(ftRcv));
                            }
                        }
                    }
                    if (row.length > 30)
                    {
                        if (row[30].getType().equals(CellType.EMPTY))
                        {
                            spilWipStatus.setFtBk(null);
                        }
                        else
                        {
                            spilWipStatus.setFtBk(row[30].getContents().trim());
                        }
                    }
                    if (row.length > 31)
                    {
                        if (row[31].getType().equals(CellType.EMPTY))
                        {
                            spilWipStatus.setFtQty(null);
                        }
                        else
                        {
                            spilWipStatus.setFtQty(row[31].getContents().trim());
                        }
                    }
                    if (row.length > 32)
                    {
                        if (row[32].getType().equals(CellType.EMPTY))
                        {
                            spilWipStatus.setFtLsQty(null);
                        }
                        else
                        {
                            spilWipStatus.setFtLsQty(row[32].getContents().trim());
                        }
                    }
                    if (row.length > 33)
                    {
                        if (row[33].getType().equals(CellType.EMPTY))
                        {
                            spilWipStatus.setFtBaking(null);
                        }
                        else
                        {
                            spilWipStatus.setFtBaking(row[33].getContents().trim());
                        }
                    }
                    if (row.length > 34)
                    {
                        if (row[34].getType().equals(CellType.EMPTY))
                        {
                            spilWipStatus.setFtpacking(null);
                        }
                        else
                        {
                            spilWipStatus.setFtpacking(row[34].getContents().trim());
                        }
                    }
                    if (row.length > 35)
                    {
                        if (row[35].getType().equals(CellType.EMPTY))
                        {
                            spilWipStatus.setFtOQCDate(null);
                        }
                         else if( row[35].getType().equals( CellType.DATE ))
                        {
                            DateCell dc = (DateCell) row[35];
                            java.util.Date ftOqcDate = null;
                            ftOqcDate = dc.getDate();

                            if ("America/Los_Angeles".equals(System.getProperty("user.timezone")))
                            {
                                Calendar cal = Calendar.getInstance(TimeZone.getTimeZone("GMT"), Locale.US);
                                cal.setTime(ftOqcDate);
                                ftOqcDate = new GregorianCalendar(cal.get(Calendar.YEAR), cal.get(Calendar.MONTH), cal.get(Calendar.DATE)).getTime();
                                spilWipStatus.setFtOQCDate(StringUtils.formatDateToString(ftOqcDate));
                            }
                            else
                            {
                                Calendar cal = new GregorianCalendar();
                                cal.setTime(ftOqcDate);
                                ftOqcDate = new GregorianCalendar(cal.get(Calendar.YEAR), cal.get(Calendar.MONTH), cal.get(Calendar.DATE)).getTime();
                                spilWipStatus.setFtOQCDate(StringUtils.formatDateToString(ftOqcDate));
                            }
                        }
                    }
                    if (row.length > 36)
                    {
                        if (row[36].getType().equals(CellType.EMPTY))
                        {
                            spilWipStatus.setFtSod(null);
                        }
                         else if( row[36].getType().equals( CellType.DATE ))
                        {
                            DateCell dc = (DateCell) row[36];
                            java.util.Date ftSod = null;
                            ftSod = dc.getDate();

                            if ("America/Los_Angeles".equals(System.getProperty("user.timezone")))
                            {
                                Calendar cal = Calendar.getInstance(TimeZone.getTimeZone("GMT"), Locale.US);
                                cal.setTime(ftSod);
                                ftSod = new GregorianCalendar(cal.get(Calendar.YEAR), cal.get(Calendar.MONTH), cal.get(Calendar.DATE)).getTime();
                                spilWipStatus.setFtSod(StringUtils.formatDateToString(ftSod));
                            }
                            else
                            {
                                Calendar cal = new GregorianCalendar();
                                cal.setTime(ftSod);
                                ftSod = new GregorianCalendar(cal.get(Calendar.YEAR), cal.get(Calendar.MONTH), cal.get(Calendar.DATE)).getTime();
                                spilWipStatus.setFtSod(StringUtils.formatDateToString(ftSod));
                            }
                        }
                    }
                    if (row.length > 37)
                    {
                        if (row[37].getType().equals(CellType.EMPTY))
                        {
                            spilWipStatus.setFtrSod(null);
                        }
                         else if( row[37].getType().equals( CellType.DATE ))
                        {
                            DateCell dc = (DateCell) row[37];
                            java.util.Date ftRsod = null;
                            ftRsod = dc.getDate();

                            if ("America/Los_Angeles".equals(System.getProperty("user.timezone")))
                            {
                                Calendar cal = Calendar.getInstance(TimeZone.getTimeZone("GMT"), Locale.US);
                                cal.setTime(ftRsod);
                                ftRsod = new GregorianCalendar(cal.get(Calendar.YEAR), cal.get(Calendar.MONTH), cal.get(Calendar.DATE)).getTime();
                                spilWipStatus.setFtrSod(StringUtils.formatDateToString(ftRsod));
                            }
                            else
                            {
                                Calendar cal = new GregorianCalendar();
                                cal.setTime(ftRsod);
                                ftRsod = new GregorianCalendar(cal.get(Calendar.YEAR), cal.get(Calendar.MONTH), cal.get(Calendar.DATE)).getTime();
                                spilWipStatus.setFtrSod(StringUtils.formatDateToString(ftRsod));
                            }
                        }
                    }
                    if (row.length > 38)
                    {
                        if (row[38].getType().equals(CellType.EMPTY))
                        {
                            spilWipStatus.setShipTo(null);
                        }
                        else
                        {
                            spilWipStatus.setShipTo(row[38].getContents().trim());
                        }
                    }
                    if (row.length > 39)
                    {
                        if (row[39].getType().equals(CellType.EMPTY))
                        {
                            spilWipStatus.setRem(null);
                        }
                        else
                        {
                            spilWipStatus.setRem(row[39].getContents().trim());
                        }
                    }
                    if (row.length > 40)
                    {
                        if (row[40].getType().equals(CellType.EMPTY))
                        {
                            spilWipStatus.setRem2(null);
                        }
                        else
                        {
                            spilWipStatus.setRem2(row[40].getContents().trim());
                        }
                    }
                    if (row.length > 41)
                    {
                        if (row[41].getType().equals(CellType.EMPTY))
                        {
                            spilWipStatus.setUncfm(null);
                        }
                        else
                        {
                            spilWipStatus.setUncfm(row[41].getContents().trim());
                        }
                    }

                    spilWipStatusList.add( spilWipStatus );
                }
            }
        }
        catch (Exception ex)
        {
            if ( !ex.getMessage().equalsIgnoreCase("Unable to recognize OLE stream") )
            {
                throw ex;
            }
        }
        return spilWipStatusList;

    }
    /**
     *
     * @param lotstatusList
     * @throws Exception
     */
    private void getSheetDetails( ArrayList lotstatusList ) throws Exception
    {
        SPILWipStatus wipStatus = null;
        FabSortLot  fabSortLot  = null;
        AssemblyLot assemblyLot = null;
        TestLot testLot         = null;
        InventoryLot dbInventoryLot = null;
        InventoryLot fgInventoryLot = null;
        InventoryLot wsInventoryLot = null;
        InventoryLot markPackLot = null;
        String lotNumber        = null;
        String partNumber       = null;
        String fablotNumber     = null;
        int assyQtyIn = 0;
        int testQtyIn = 0;
        int fgQtyIn   = 0;

        if (lotstatusList == null)
        {
            return;
        }

        Iterator iterator = lotstatusList.iterator();
        try
        {
            while (iterator.hasNext())
            {
                wipStatus = (SPILWipStatus) iterator.next();

                fablotNumber    = wipStatus.getCustomerLot();
                partNumber      = wipStatus.getDevice();
                lotNumber       = wipStatus.getProdLot();

                if( lotNumber == null )
                {
                    lotNumber = fablotNumber;
                }

                assyQtyIn =  wipStatus.getAssemblyQtyIn();
                testQtyIn = wipStatus.getTestQtyIn();
                fgQtyIn   = wipStatus.getFgQtyIn();
                
                if ( lotNumber != null && lotNumber.trim().length() > 0 && partNumber != null && partNumber.trim().length() > 0)
                {
                    /* Wafer Store Lots*/
                    if (wipStatus.getWsbk() != null && !"0".equalsIgnoreCase( wipStatus.getWsbk() )  )
                    {
                        wsInventoryLot = (InventoryLot) m_waferBankDataMap.get(partNumber + fablotNumber);
                        if (wsInventoryLot == null)
                        {
                            wsInventoryLot = new InventoryLot(VENDOR_NAME_SPIL_CHINA, LOCATION_TYPE_WAFER_STORE );
                            m_waferBankDataMap.put(partNumber + fablotNumber, wsInventoryLot);
                        }

                        int quantity = 0;
                        if (wsInventoryLot.getWaferQtyIn() != null)
                        {
                            try
                            {
                                quantity = Integer.parseInt( wsInventoryLot.getWaferQtyIn() );
                            }
                            catch (NumberFormatException npe) {
                            }
                            try
                            {
                                quantity += Integer.parseInt( wipStatus.getWsbk() );
                            }
                            catch (NumberFormatException npe) {
                            }
                        }
                        else
                        {
                            try
                            {
                                quantity = Integer.parseInt(wipStatus.getWsbk());
                            }
                            catch (NumberFormatException npe) {
                            }
                        }

                        if (quantity > 0)
                        {
                            wsInventoryLot.setLotNumber(fablotNumber);
                            wsInventoryLot.setPartNumber(partNumber);
                            wsInventoryLot.setDateIn(wipStatus.getWsrcv());
                            wsInventoryLot.setWaferQtyIn(quantity + "");
                            wsInventoryLot.setStatus(LOT_STATUS_RUNNING);
                            wsInventoryLot.setFabLotNumber(fablotNumber);
                            wsInventoryLot.setCategory(LOT_CATEGORY_TYPE_PROD);
                        }
                    }

                    //SORT
                    if ( wipStatus.getProbe() != null && !"0".equalsIgnoreCase(wipStatus.getProbe() )
                           || wipStatus.getWsfgWafer() != null && !"0".equalsIgnoreCase(wipStatus.getWsfgWafer() )
                            || wipStatus.getWsTran() != null && !"0".equalsIgnoreCase(wipStatus.getWsTran() ) )
                    {
                        log( lotNumber + "in Sort ");

                        fabSortLot = (FabSortLot) m_sortDataMap.get( partNumber + fablotNumber );

                        if (fabSortLot == null)
                        {
                            fabSortLot = new FabSortLot(VENDOR_NAME_SPIL_CHINA, WIP_TYPE_SORT );
                            m_sortDataMap.put( partNumber + fablotNumber, fabSortLot);
                        }

                        if (wipStatus.getProbe() != null && !"0".equalsIgnoreCase(wipStatus.getProbe() ) )
                        {
                            fabSortLot = (FabSortLot) m_sortDataMap.get(partNumber + fablotNumber );

                            if( fabSortLot.getQtyIn() != null )
                            {
                                try
                                {
                                    fabSortLot.setQtyIn( ( Integer.parseInt( fabSortLot.getQtyIn() ) + Integer.parseInt( wipStatus.getProbe() ) ) + "" );
                                }
                                catch(Exception e)
                                {
                                    fabSortLot.setQtyIn( wipStatus.getProbe() );
                                }
                            }
                            else
                            {
                                fabSortLot.setQtyIn( wipStatus.getProbe() );
                            }

                            fabSortLot.setStage("Probe");
                        }

                        if ( wipStatus.getWsfgWafer() != null && !"0".equalsIgnoreCase(wipStatus.getWsfgWafer() ) )
                        {
                           fabSortLot = (FabSortLot) m_sortDataMap.get( partNumber + fablotNumber );

                            if( fabSortLot.getQtyIn() != null )
                            {
                                try
                                {
                                    fabSortLot.setQtyIn( ( Integer.parseInt( fabSortLot.getQtyIn() ) + Integer.parseInt( wipStatus.getWsfgWafer() ) ) + "" );
                                }
                                catch(Exception e)
                                {
                                    fabSortLot.setQtyIn( wipStatus.getWsfgWafer() );
                                }
                            }
                            else
                            {
                                fabSortLot.setQtyIn( wipStatus.getWsfgWafer() );
                            }

                            fabSortLot.setStage("After Probe");
                        }

                        if ( wipStatus.getWsTran() != null && !"0".equalsIgnoreCase(wipStatus.getWsTran() ) &&  Integer.parseInt( wipStatus.getWsTran() ) <= 25 )
                        {
                           fabSortLot = (FabSortLot) m_sortDataMap.get( partNumber + fablotNumber );

                            if( fabSortLot.getQtyIn() != null )
                            {
                                try
                                {
                                    fabSortLot.setQtyIn( ( Integer.parseInt( fabSortLot.getQtyIn() ) + Integer.parseInt( wipStatus.getWsTran() ) ) + "" );
                                }
                                catch(Exception e)
                                {
                                    fabSortLot.setQtyIn( wipStatus.getWsTran() );
                                }
                            }
                            else
                            {
                                fabSortLot.setQtyIn( wipStatus.getWsTran() );
                            }

                            fabSortLot.setStage("InHouse Transit");
                        }

                        fabSortLot.setCustProduct( partNumber );
                        fabSortLot.setLotId( fablotNumber );
                        fabSortLot.setForcastedDateOut( wipStatus.getWsSOD() );
                        fabSortLot.setPkgType( wipStatus.getPackageType() );
                        fabSortLot.setLeadCount( wipStatus.getLeadCount() );
                        fabSortLot.setDateIn( wipStatus.getWsrcv() );
                        fabSortLot.setFabLotNumber( fablotNumber );
                        fabSortLot.setLotStatus( LOT_STATUS_RUNNING );
                        fabSortLot.setLotCategory( LOT_CATEGORY_TYPE_PROD );

                    }

                    // Exceptional CASE SORT
                    if ( wipStatus.getAsBkWafer() != null && !"0".equalsIgnoreCase(wipStatus.getAsBkWafer())
                           && wipStatus.getAsBkWafer().equalsIgnoreCase( wipStatus.getAsActualQty() ) == true )
                    {
                       log( lotNumber + "in Sort ");

                        fabSortLot = (FabSortLot) m_sortDataMap.get(partNumber + fablotNumber );

                        if (fabSortLot == null)
                        {
                            fabSortLot = new FabSortLot(VENDOR_NAME_SPIL_CHINA, WIP_TYPE_SORT );
                            m_sortDataMap.put( partNumber + fablotNumber, fabSortLot);
                        }
                        int quantity = 0;
                        if (fabSortLot.getQtyIn() != null)
                        {
                            try
                            {
                                quantity = Integer.parseInt(fabSortLot.getQtyIn());
                            }
                            catch (NumberFormatException ignore) {
                            }
                            try
                            {
                                quantity += Integer.parseInt(wipStatus.getAsBkWafer());
                            }
                            catch (NumberFormatException nfe) {
                            }
                        }
                        else
                        {
                            try
                            {
                                quantity = Integer.parseInt( wipStatus.getAsBkWafer() );
                            }
                            catch (NumberFormatException ignore) {
                            }
                        }

                        fabSortLot.setCustProduct( partNumber );
                        fabSortLot.setLotId( fablotNumber );
                        fabSortLot.setQtyIn(quantity + "");
                        fabSortLot.setDateIn(wipStatus.getAsRcvDate());
                        fabSortLot.setPkgType( wipStatus.getPackageType() );
                        fabSortLot.setLeadCount( wipStatus.getLeadCount() );
                        fabSortLot.setFabLotNumber( fablotNumber );
                        fabSortLot.setLotStatus( LOT_STATUS_RUNNING );
                        fabSortLot.setLotCategory( LOT_CATEGORY_TYPE_PROD );
                    }

                    //DIE BANK
                    if ( wipStatus.getAsBkDie() != null && !"0".equalsIgnoreCase( wipStatus.getAsBkDie() ) )
                    {
                        if ( wipStatus.getAsBkWafer() != null && !"0".equalsIgnoreCase(wipStatus.getAsBkWafer())
                           && wipStatus.getAsBkWafer().equalsIgnoreCase( wipStatus.getAsActualQty() ) == false )
                        {
                            log( lotNumber +" In Die Bank");

                            dbInventoryLot = (InventoryLot)m_dieBankDataMap.get( partNumber + lotNumber + fablotNumber );

                            if ( dbInventoryLot == null )
                            {
                                dbInventoryLot = new InventoryLot( VENDOR_NAME_SPIL_CHINA, LOCATION_TYPE_DIE_BANK );
                                m_dieBankDataMap.put( partNumber + lotNumber + fablotNumber, dbInventoryLot );
                            }

                            int quantity = 0;
                            if (dbInventoryLot.getQtyIn() != null)
                            {
                                try
                                {
                                    quantity = Integer.parseInt(dbInventoryLot.getQtyIn());
                                }
                                catch (NumberFormatException ignore) {
                                }
                                try
                                {
                                    quantity += Integer.parseInt(wipStatus.getAsBkDie());
                                }
                                catch (NumberFormatException nfe) {
                                }
                            }
                            else
                            {
                                try
                                {
                                    quantity = Integer.parseInt( wipStatus.getAsBkDie() );
                                }
                                catch (NumberFormatException ignore) {
                                }
                            }

                            dbInventoryLot.setPartNumber( partNumber );
                            dbInventoryLot.setLotNumber( lotNumber );
                            dbInventoryLot.setQtyIn(quantity + "");
                            dbInventoryLot.setDateIn( wipStatus.getAsRcvDate() );
                            dbInventoryLot.setFabLotNumber( fablotNumber );
                            dbInventoryLot.setPkgType( wipStatus.getPackageType() );
                            dbInventoryLot.setLeadCount( wipStatus.getLeadCount() );
                            dbInventoryLot.setStatus( LOT_STATUS_RUNNING );
                            dbInventoryLot.setLotCategory( LOT_CATEGORY_TYPE_PROD );
                        }
                    }
                    
                     //ASSEMBLY RECEIVE
                    if ( wipStatus.getAsActualQty() != null && wipStatus.getAsActualQty().trim().equalsIgnoreCase("0") == false
                         && wipStatus.getAsBkDie() != null && "0".equalsIgnoreCase( wipStatus.getAsBkDie() ) )
                    {
                        if( testQtyIn == 0 && fgQtyIn == 0 && assyQtyIn == 0 )
                        {
                            log( "In Assembly Receive" + lotNumber ) ;
                            assemblyLot = (AssemblyLot) m_assemblyDataMap.get( partNumber + lotNumber + fablotNumber );
                            if (assemblyLot == null)
                            {
                                assemblyLot = new AssemblyLot(VENDOR_NAME_SPIL_CHINA );
                                m_assemblyDataMap.put( partNumber + lotNumber + fablotNumber, assemblyLot);
                            }

                            assemblyLot.setStage("Assy Receive");
                            assemblyLot.setQtyIn( wipStatus.getAsActualQty() );
                            assemblyLot.setLotNumber( lotNumber );
                            assemblyLot.setDeviceName( partNumber );
                            if ( wipStatus.getRem() != null && wipStatus.getRem().indexOf("Hold") != -1 )
                            {
                               assemblyLot.setStatus(LOT_STATUS_HOLD);
                               assemblyLot.setHoldReason( wipStatus.getRem() );
                            }
                            else
                            {
                               assemblyLot.setStatus(LOT_STATUS_RUNNING);
                            }
                            assemblyLot.setFabLotNumber( fablotNumber );
                            assemblyLot.setPackageType( wipStatus.getPackageType() );
                            assemblyLot.setCategory( LOT_CATEGORY_TYPE_PROD );
                            assemblyLot.setLeadCount( wipStatus.getLeadCount() );
                            assemblyLot.setDateIn( wipStatus.getLoadDate() );
                            assemblyLot.setExpectedDateOut( wipStatus.getAsSod() );
                            assemblyLot.setProcessType( WIP_TYPE_ASSEMBLY );
                        }
                    }
                     //ASSEMBLY
                    if (  wipStatus.getOpticalInspect() != null && wipStatus.getOpticalInspect().equalsIgnoreCase("0") == false
                            || wipStatus.getDieAttach() != null && wipStatus.getDieAttach().equalsIgnoreCase("0") == false
                            || wipStatus.getWireBond() != null && wipStatus.getWireBond().equalsIgnoreCase("0") == false
                            || wipStatus.getMolding() != null && wipStatus.getMolding().equalsIgnoreCase("0") == false
                            || wipStatus.getPlating() != null && wipStatus.getPlating().equalsIgnoreCase("0") == false
                            || wipStatus.getForming() != null && wipStatus.getForming().equalsIgnoreCase("0") == false
                            || wipStatus.getPacking() != null && wipStatus.getPacking().equalsIgnoreCase("0") == false
                            || wipStatus.getAsTran() != null && wipStatus.getAsTran().equalsIgnoreCase("0") == false )
                   {
                        log( "In Assembly" + lotNumber ) ;

                        assemblyLot = (AssemblyLot) m_assemblyDataMap.get( partNumber + lotNumber + fablotNumber );
                        if (assemblyLot == null)
                        {
                            assemblyLot = new AssemblyLot(VENDOR_NAME_SPIL_CHINA );
                            m_assemblyDataMap.put( partNumber + lotNumber + fablotNumber, assemblyLot);
                        }

                        // FOR SETTING THE QTY IN
                        if( wipStatus.getOpticalInspect() != null && wipStatus.getOpticalInspect().equalsIgnoreCase("0") == false )
                        {
                            assemblyLot = (AssemblyLot) m_assemblyDataMap.get(partNumber + lotNumber + fablotNumber);
                            if( assemblyLot.getQtyIn() != null )
                            {
                                try
                                {
                                    assemblyLot.setQtyIn( ( Integer.parseInt( assemblyLot.getQtyIn() ) + Integer.parseInt( wipStatus.getOpticalInspect() ) ) + "" );
                                }
                                catch(Exception e)
                                {
                                    assemblyLot.setQtyIn( wipStatus.getOpticalInspect() );
                                }
                            }
                            else
                            {
                                assemblyLot.setQtyIn( wipStatus.getOpticalInspect() );
                            }
                            if ( assemblyLot.getStage() != null )
                            {
                                assemblyLot.setStage ( assemblyLot.getStage() +", 2/Optical") ;
                            }
                            else
                            {
                                assemblyLot.setStage("2/Optical");
                            }
                        }
                        if( wipStatus.getDieAttach() != null && wipStatus.getDieAttach().equalsIgnoreCase("0") == false )
                        {
                           assemblyLot = (AssemblyLot) m_assemblyDataMap.get(partNumber + lotNumber + fablotNumber);
                           if( assemblyLot.getQtyIn() != null )
                           {
                                try
                                {
                                    assemblyLot.setQtyIn( ( Integer.parseInt( assemblyLot.getQtyIn() ) + Integer.parseInt( wipStatus.getDieAttach() ) ) + "" );
                                }
                                catch(Exception e)
                                {
                                    assemblyLot.setQtyIn( wipStatus.getDieAttach() );
                                }
                            }
                            else
                            {
                                assemblyLot.setQtyIn( wipStatus.getDieAttach() );
                            }
                            if ( assemblyLot.getStage() != null )
                            {
                                assemblyLot.setStage ( assemblyLot.getStage() +", Die Attach") ;
                            }
                            else
                            {
                                assemblyLot.setStage("Die Attach");
                            }
                        }
                        if( wipStatus.getWireBond() != null && wipStatus.getWireBond().equalsIgnoreCase("0") == false )
                        {
                           assemblyLot = (AssemblyLot) m_assemblyDataMap.get(partNumber + lotNumber + fablotNumber);
                           if( assemblyLot.getQtyIn() != null )
                           {
                                try
                                {
                                    assemblyLot.setQtyIn( ( Integer.parseInt( assemblyLot.getQtyIn() ) + Integer.parseInt( wipStatus.getWireBond() ) ) + "" );
                                }
                                catch(Exception e)
                                {
                                    assemblyLot.setQtyIn( wipStatus.getWireBond() );
                                }
                            }
                            else
                            {
                                assemblyLot.setQtyIn( wipStatus.getWireBond() );
                            }
                            if ( assemblyLot.getStage() != null )
                            {
                                assemblyLot.setStage ( assemblyLot.getStage() +", Wire Bond") ;
                            }
                            else
                            {
                                assemblyLot.setStage("Wire Bond");
                            }
                        }
                        if( wipStatus.getMolding() != null && wipStatus.getMolding().equalsIgnoreCase("0") == false )
                        {
                           assemblyLot = (AssemblyLot) m_assemblyDataMap.get(partNumber + lotNumber + fablotNumber);

                           if( assemblyLot.getQtyIn() != null )
                           {
                                try
                                {
                                    assemblyLot.setQtyIn( ( Integer.parseInt( assemblyLot.getQtyIn() ) + Integer.parseInt( wipStatus.getMolding() ) ) + "" );
                                }
                                catch(Exception e)
                                {
                                    assemblyLot.setQtyIn( wipStatus.getMolding() );
                                }
                            }
                            else
                            {
                                assemblyLot.setQtyIn( wipStatus.getMolding() );
                            }
                            if ( assemblyLot.getStage() != null )
                            {
                                assemblyLot.setStage ( assemblyLot.getStage() +", Molding") ;
                            }
                            else
                            {
                                assemblyLot.setStage("Molding");
                            }
                        }

                        if( wipStatus.getPlating() != null && wipStatus.getPlating().equalsIgnoreCase("0") == false )
                        {
                           assemblyLot = (AssemblyLot) m_assemblyDataMap.get(partNumber + lotNumber + fablotNumber);
                           if( assemblyLot.getQtyIn() != null )
                           {
                                try
                                {
                                    assemblyLot.setQtyIn( ( Integer.parseInt( assemblyLot.getQtyIn() ) + Integer.parseInt( wipStatus.getPlating() ) ) + "" );
                                }
                                catch(Exception e)
                                {
                                    assemblyLot.setQtyIn( wipStatus.getPlating() );
                                }
                            }
                            else
                            {
                                assemblyLot.setQtyIn( wipStatus.getPlating() );
                            }
                            if ( assemblyLot.getStage() != null )
                            {
                                assemblyLot.setStage ( assemblyLot.getStage() +", Plating") ;
                            }
                            else
                            {
                                assemblyLot.setStage("Plating");
                            }
                        }

                        if( wipStatus.getForming() != null && wipStatus.getForming().equalsIgnoreCase("0") == false )
                        {
                            assemblyLot = (AssemblyLot) m_assemblyDataMap.get(partNumber + lotNumber + fablotNumber);
                           if( assemblyLot.getQtyIn() != null )
                           {
                                try
                                {
                                    assemblyLot.setQtyIn( ( Integer.parseInt( assemblyLot.getQtyIn() ) + Integer.parseInt( wipStatus.getForming() ) ) + "" );
                                }
                                catch(Exception e)
                                {
                                    assemblyLot.setQtyIn( wipStatus.getForming() );
                                }
                            }
                            else
                            {
                                assemblyLot.setQtyIn( wipStatus.getForming() );
                            }
                            if ( assemblyLot.getStage() != null )
                            {
                                assemblyLot.setStage ( assemblyLot.getStage() +", Forming") ;
                            }
                            else
                            {
                                assemblyLot.setStage("Forming");
                            }
                        }

                        if( wipStatus.getPacking() != null && wipStatus.getPacking().equalsIgnoreCase("0") == false )
                        {
                            assemblyLot = (AssemblyLot) m_assemblyDataMap.get(partNumber + lotNumber + fablotNumber);
                           if( assemblyLot.getQtyIn() != null )
                           {
                                try
                                {
                                    assemblyLot.setQtyIn( ( Integer.parseInt( assemblyLot.getQtyIn() ) + Integer.parseInt( wipStatus.getPacking() ) ) + "" );
                                }
                                catch(Exception e)
                                {
                                    assemblyLot.setQtyIn( wipStatus.getPacking() );
                                }
                            }
                            else
                            {
                                assemblyLot.setQtyIn( wipStatus.getPacking() );
                            }
                            if ( assemblyLot.getStage() != null )
                            {
                                assemblyLot.setStage ( assemblyLot.getStage() +", Packing") ;
                            }
                            else
                            {
                                assemblyLot.setStage("Packing");
                            }
                        }

                        if( wipStatus.getAsTran() != null && wipStatus.getAsTran().equalsIgnoreCase("0") == false )
                        {
                           assemblyLot = (AssemblyLot) m_assemblyDataMap.get(partNumber + lotNumber + fablotNumber);
                           if( assemblyLot.getQtyIn() != null )
                           {
                                try
                                {
                                    assemblyLot.setQtyIn( ( Integer.parseInt( assemblyLot.getQtyIn() ) + Integer.parseInt( wipStatus.getAsTran() ) ) + "" );
                                }
                                catch(Exception e)
                                {
                                    assemblyLot.setQtyIn( wipStatus.getAsTran() );
                                }
                            }
                            else
                            {
                                assemblyLot.setQtyIn( wipStatus.getAsTran() );
                            }
                            if ( assemblyLot.getStage() != null )
                            {
                                assemblyLot.setStage ( assemblyLot.getStage() +", InHouse Transit") ;
                            }
                            else
                            {
                                assemblyLot.setStage("InHouse Transit");
                            }   
                        }

                        assemblyLot.setLotNumber( lotNumber );
                        assemblyLot.setDeviceName( partNumber );
                        if ( wipStatus.getRem() != null && wipStatus.getRem().indexOf("Hold") != -1 )
                        {
                           assemblyLot.setStatus(LOT_STATUS_HOLD);
                           assemblyLot.setHoldReason( wipStatus.getRem() );
                        }
                        else
                        {
                           assemblyLot.setStatus(LOT_STATUS_RUNNING);
                        }
                        assemblyLot.setFabLotNumber( fablotNumber );
                        assemblyLot.setPackageType( wipStatus.getPackageType() );
                        assemblyLot.setCategory( LOT_CATEGORY_TYPE_PROD );
                        assemblyLot.setLeadCount( wipStatus.getLeadCount() );
                        assemblyLot.setDateIn( wipStatus.getLoadDate() );
                        assemblyLot.setExpectedDateOut( wipStatus.getAsSod() );
                        assemblyLot.setProcessType( WIP_TYPE_ASSEMBLY );
                    }

                    // FT Process
                    if ( wipStatus.getFtBk() != null && !"0".equalsIgnoreCase( wipStatus.getFtBk() )
                        || wipStatus.getFtQty() != null && !"0".equalsIgnoreCase( wipStatus.getFtQty() ) )
                    {
                        log(lotNumber + "In Test FT");

                        testLot = (TestLot) m_ftDataMap.get( partNumber + lotNumber + fablotNumber );
                        if (testLot == null)
                        {
                            testLot = new TestLot(VENDOR_NAME_SPIL_CHINA);
                            m_ftDataMap.put(partNumber + lotNumber + fablotNumber, testLot);
                        }

                       // FOR SETTING THE QTY IN
                       if( wipStatus.getFtBk() != null && wipStatus.getFtBk().equalsIgnoreCase("0") == false )
                       {
                           testLot = (TestLot) m_ftDataMap.get( partNumber + lotNumber + fablotNumber );
                           if( testLot.getQtyIn() != null )
                           {
                                try
                                {
                                    testLot.setQtyIn( ( Integer.parseInt( testLot.getQtyIn() ) + Integer.parseInt( wipStatus.getFtBk() ) ) + "" );
                                }
                                catch(Exception e)
                                {
                                    testLot.setQtyIn( wipStatus.getFtBk() );
                                }
                            }
                            else
                            {
                                testLot.setQtyIn( wipStatus.getFtBk() );
                            }
                            if ( testLot.getStage() != null )
                            {
                                testLot.setStage( testLot.getStage() + ", FtBank");
                            }
                            else
                            {
                                testLot.setStage("FtBank");
                            }
                        }
                        if( wipStatus.getFtQty() != null && wipStatus.getFtQty().equalsIgnoreCase("0") == false )
                        {
                            testLot = (TestLot) m_ftDataMap.get( partNumber + lotNumber + fablotNumber );
                            if( testLot.getQtyIn() != null )
                            {
                                try
                                {
                                    testLot.setQtyIn( ( Integer.parseInt( testLot.getQtyIn() ) + Integer.parseInt( wipStatus.getFtQty() ) ) + "" );
                                }
                                catch(Exception e)
                                {
                                    testLot.setQtyIn( wipStatus.getFtQty() );
                                }
                             }
                             else
                             {
                                testLot.setQtyIn( wipStatus.getFtQty() );
                             }
                             if ( testLot.getStage() != null )
                             {
                                testLot.setStage( testLot.getStage() + ", F/T");
                             }
                             else
                             {
                                testLot.setStage("F/T");
                             }
                        }

                        testLot.setPartNumber( partNumber );
                        testLot.setLotNumber( lotNumber );
                        testLot.setFabLotNumber( fablotNumber );
                        testLot.setDateIn(wipStatus.getFtRcvDate() );
                        testLot.setExpecetedDate( wipStatus.getFtSod() );
                        testLot.setLeadCount( wipStatus.getLeadCount() );
                        testLot.setPackageType( wipStatus.getPackageType() );
                        if ( wipStatus.getRem() != null && wipStatus.getRem().indexOf("Hold") != -1 )
                        {
                           testLot.setStatus(LOT_STATUS_HOLD);
                           testLot.setHoldReason( wipStatus.getRem() );
                        }
                        else
                        {
                           testLot.setStatus(LOT_STATUS_RUNNING);
                        }
                        testLot.setPsType( LOCATION_TYPE_FT );
                        testLot.setRevisedDateOut(wipStatus.getFtrSod());
                        testLot.setCategory( LOT_CATEGORY_TYPE_PROD );
                    }

                    // BAKE & DRY
                    if ( wipStatus.getFtLsQty() != null && !"0".equalsIgnoreCase( wipStatus.getFtLsQty() )
                        || wipStatus.getFtBaking() != null && !"0".equalsIgnoreCase( wipStatus.getFtBaking() ) )
                     {

                         log( "In Bake & Dry Mark & Pack " + lotNumber );

                        markPackLot = (InventoryLot) m_markPackDataMap.get( partNumber + lotNumber + fablotNumber);

                        if (markPackLot == null)
                        {
                            markPackLot = new InventoryLot(VENDOR_NAME_SPIL_CHINA, LOCATION_TYPE_MARK_AND_PACK );
                            m_markPackDataMap.put( partNumber + lotNumber + fablotNumber, markPackLot);
                        }

                         if( wipStatus.getFtLsQty() != null && wipStatus.getFtLsQty().equalsIgnoreCase("0") == false )
                        {
                            markPackLot = (InventoryLot) m_markPackDataMap.get( partNumber + lotNumber + fablotNumber);

                            if( markPackLot.getQtyIn() != null )
                            {
                                try
                                {
                                    markPackLot.setQtyIn( ( Integer.parseInt( markPackLot.getQtyIn() ) + Integer.parseInt( wipStatus.getFtLsQty() ) ) + "" );
                                }
                                catch(Exception e)
                                {
                                    markPackLot.setQtyIn( wipStatus.getFtLsQty() );
                                }
                             }
                             else
                             {
                                markPackLot.setQtyIn( wipStatus.getFtLsQty() );
                             }
                             if ( markPackLot.getStage() != null )
                             {
                                markPackLot.setStage( markPackLot.getStage() + ", Lead Scan");
                             }
                             else
                             {
                                markPackLot.setStage("Lead Scan");
                             }
                        }
                        if( wipStatus.getFtBaking() != null && wipStatus.getFtBaking().equalsIgnoreCase("0") == false )
                        {
                            markPackLot = (InventoryLot) m_markPackDataMap.get( partNumber + lotNumber + fablotNumber);

                            if( markPackLot.getQtyIn() != null )
                            {
                                try
                                {
                                    markPackLot.setQtyIn( ( Integer.parseInt( markPackLot.getQtyIn() ) + Integer.parseInt( wipStatus.getFtBaking() ) ) + "" );
                                }
                                catch(Exception e)
                                {
                                    markPackLot.setQtyIn( wipStatus.getFtBaking() );
                                }
                             }
                             else
                             {
                                markPackLot.setQtyIn( wipStatus.getFtBaking() );
                             }

                             if ( markPackLot.getStage() != null )
                             {
                                markPackLot.setStage( testLot.getStage() + ", Bake");
                             }
                             else
                             {
                                markPackLot.setStage("Bake");
                             }
                        }

                        markPackLot.setLotNumber( lotNumber );
                        markPackLot.setPartNumber( partNumber );
                        if ( wipStatus.getRem() != null && wipStatus.getRem().indexOf("Hold") != -1 )
                        {
                           markPackLot.setStatus(LOT_STATUS_HOLD);
                           markPackLot.setHoldReason( wipStatus.getRem() );
                        }
                        else
                        {
                           markPackLot.setStatus(LOT_STATUS_RUNNING);
                        }
                        markPackLot.setFabLotNumber( fablotNumber );
                        markPackLot.setCategory( LOT_CATEGORY_TYPE_PROD );
                        markPackLot.setDateIn( wipStatus.getFtRcvDate() );
                        markPackLot.setPkgType( wipStatus.getPackageType() );
                        markPackLot.setLeadCount( wipStatus.getLeadCount() );
                        markPackLot.setExpectedDate( wipStatus.getFtSod() );
                    }

                     // FG Process
                    if (wipStatus.getFtpacking() != null && !"0".equalsIgnoreCase( wipStatus.getFtpacking() ) )
                    {
                        log(lotNumber + "In FG");

                        fgInventoryLot = (InventoryLot)m_fgDataMap.get( partNumber + lotNumber + fablotNumber );

                        if ( fgInventoryLot == null )
                        {
                            fgInventoryLot = new InventoryLot( VENDOR_NAME_SPIL_CHINA, LOCATION_TYPE_FG );
                            m_fgDataMap.put( partNumber + lotNumber + fablotNumber, fgInventoryLot );
                        }

                        fgInventoryLot.setPartNumber( partNumber );
                        fgInventoryLot.setLotNumber( lotNumber );
                        fgInventoryLot.setQtyIn( wipStatus.getFtpacking() );
                        fgInventoryLot.setDateIn( wipStatus.getFtRcvDate() );
                        fgInventoryLot.setExpectedDate( wipStatus.getFtSod() );
                        fgInventoryLot.setFabLotNumber( fablotNumber );
                        fgInventoryLot.setPkgType( wipStatus.getPackageType() );
                        fgInventoryLot.setLeadCount( wipStatus.getLeadCount() );
                        fgInventoryLot.setStatus( LOT_STATUS_RUNNING );
                        fgInventoryLot.setLotCategory( LOT_CATEGORY_TYPE_PROD );
                    }
                }
            }
        }
        catch( Exception e)
        {
            System.out.println(" !!ERROR !! ERROR Exception while get getting the SPIL China Wip Details");
            e.printStackTrace();
            throw e;
        }
    }
    
    private void log( String message )
    {
        if ( message == null ) return;

        if ( m_parserLogger != null )
        {
            m_parserLogger.logDebug( message + LINE_SEPARATOR );
        }
        else
        {
            System.out.println( message );
        }
    } // end of log method
}
